﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using MySql.Data.MySqlClient;

namespace SqlMidasala
{
    class sql
    {
        #region Variables
        private Globals g = new Globals();

        private Validate v = new Validate();

        private string server;

        private string port;

        private string db;

        private string user;

        private string pass;

        private string charSet;

        private string conString = null;

        private string query = null;

        private static MySqlConnection link; // Tenging við gagnagrunn
        private MySqlCommand mysqlQ; // sql fyrirspurn
        private MySqlDataReader mysqlR = null; // Les yfir sql gagnagrunninn
        #endregion

        /// <summary>
       /// Sets the connection to the MySql server
       /// </summary>
       /// <returns></returns>
        public bool SetLink()
        {
            server = "localhost";

            port = "3306";

            db = "skraningar";

            user = "roots";

            pass = "toor";

            charSet = "utf8";

            conString = string.Format("server={0};port={1};userid={2};password={3};Charset={4};"
            //     {0}    {1}   {2}   {3}     {4}
                , server, port, user, pass, charSet);

            try
            {
                link = new MySqlConnection(conString);
                if (SelectDB())
                    return true;
                else
                    return false;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message, "Error: " + ex.Number, MessageBoxButton.OK, MessageBoxImage.Error);
                return false;
            }
        }

        /// <summary>
        /// Selects database to avoid crash
        /// </summary>
        /// <returns></returns>
        private bool SelectDB()
        {
            if (OpenConnection())
            {
                try
                {
                    query = "USE " + db;

                    mysqlQ = new MySqlCommand(query, link);
                    mysqlQ.ExecuteNonQuery();

                    CloseConnection();

                    return true;
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show(ex.Message, "Error: " + ex.Number, MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
            return false;
        }

        /// <summary>
        /// Add guest to database
        /// </summary>
        /// <param name="id">Icelandic id number (kennitala)</param>
        /// <param name="name">Name of guest</param>
        /// <param name="pNumber">Guests phone number</param>
        /// <param name="info">Other relevant info</param>
        public void AddGuset(string id, string name, string pNumber, string info)
        {
            if (OpenConnection())
            {
                object[] parms = { id, name, pNumber, info };
                try
                {
                    // splæsir saman strengnum
                    query =
                            string.Format("INSERT INTO gestur (id, nafn, Simanumer, Athugasemdir)"
                          + "VALUES ('{0}','{1}','{2}','{3}')"
                          ,parms);

                    mysqlQ = new MySqlCommand(query, link);
                    mysqlQ.ExecuteNonQuery();

                    CloseConnection();
                    UpdateLists();
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show(ex.Message, "Error: " + ex.Number, MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
        }

        /// <summary>
        /// Updates guest
        /// </summary>
        /// <param name="id">Replacement id</param>
        /// <param name="name">New name</param>
        /// <param name="pNumber">New phone number</param>
        /// <param name="info">New info</param>
        /// <param name="oldID">Current id(kennitala)</param>
        public void EditGuest(string id, string name, string pNumber, string info,string oldID)
        {
            if (OpenConnection())
            {
                object[] parms = { id, name ,pNumber, info,oldID};
                try
                {
                    // splæsir saman strengnum
                    query =
                            string.Format("UPDATE gestur "
                          + "SET id={0},Nafn='{1}',Simanumer='{2}',Athugasemdir='{3}' "
                          + "WHERE id='{4}'"
                          , parms);

                    mysqlQ = new MySqlCommand(query, link);
                    mysqlQ.ExecuteNonQuery();

                    CloseConnection();
                    UpdateLists();
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show(ex.Message, "Error: " + ex.Number, MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
        }

        /// <summary>
        /// Removes guest based on icelandic id(kennitala)
        /// </summary>
        /// <param name="id">Icelandic id or kennitala</param>
        public void RemoveGuest(string id)
        {
            if (OpenConnection())
            {
                try
                {
                    query =
                            string.Format("DELETE FROM gestur "
                          + "WHERE id='{0}'"
                          , id);

                    mysqlQ = new MySqlCommand(query, link);
                    mysqlQ.ExecuteNonQuery();

                    CloseConnection();
                    UpdateLists();
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show(ex.Message,"Error: " + ex.Number,MessageBoxButton.OK,MessageBoxImage.Error);
                }
            }
        }

        /// <summary>
        /// Adds Event to database
        /// </summary>
        /// <param name="name">Name of event</param>
        /// <param name="place">Place of event</param>
        /// <param name="price">Ticket price</param>
        /// <param name="count">Total ticket number</param>
        /// <param name="date">Event date</param>
        /// <param name="time">Time event starts</param>
        public void AddEvent(string name, string place, double price, int count, string date,string time)
        {
            if (OpenConnection())
            {
                object[] parms = { name, place, price, count, date, time };
                try
                {
                    // splæsir saman strengnum
                    query =
                            string.Format("INSERT INTO vidburdur (nafn, stadsetning, Mida_verd, Mida_Fjoldi, dagsetning, timasetning)"
                          + "VALUES ('{0}','{1}','{2}','{3}','{4}','{5}')"
                          , parms);

                    mysqlQ = new MySqlCommand(query, link);
                    mysqlQ.ExecuteNonQuery();

                    CloseConnection();
                    UpdateLists();
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show(ex.Message, "Error: " + ex.Number, MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
        }

        /// <summary>
        /// Updates event in database from event name
        /// </summary>
        /// <param name="name">New name of event</param>
        /// <param name="place">New event place</param>
        /// <param name="price">New pricetag</param>
        /// <param name="count">New number oof tickets</param>
        /// <param name="date">New date</param>
        /// <param name="time">New time</param>
        /// <param name="oldName">Current name</param>
        public void EditEvent(string name,string place,string price, string count,string date,string time,string oldName)
        {
            string[] ids = GetEventId(oldName);
            string id = ids[0];
            if (OpenConnection())
            {
                object[] parms = { name, place, price, count, date, time, id};
                try
                {
                    // splæsir saman strengnum
                    query =
                            string.Format("UPDATE vidburdur "
                          + "SET Nafn='{0}',Stadsetning='{1}',Mida_Verd={2},Mida_Fjoldi={3},Dagsetning='{4}',Timasetning='{5}' "
                          + "WHERE id='{6}'"
                          , parms);

                    mysqlQ = new MySqlCommand(query, link);
                    mysqlQ.ExecuteNonQuery();

                    CloseConnection();
                    UpdateLists();
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show(ex.Message, "Error: " + ex.Number, MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
        }

        /// <summary>
        /// Deletes event from database useing name
        /// </summary>
        /// <param name="name">Current name</param>
        public void RemoveEvent(string name)
        {
            string[] ids = GetEventId(name);
            string id = ids[0];
            if (OpenConnection())
            {
                try
                {
                    query =
                            string.Format("DELETE FROM vidburdur "
                          + "WHERE id='{0}'"
                          , id);

                    mysqlQ = new MySqlCommand(query, link);
                    mysqlQ.ExecuteNonQuery();

                    CloseConnection();
                    UpdateLists();
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show(ex.Message, "Error: " + ex.Number, MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
        }

        /// <summary>
        /// Gets id of event from name retuns -1 if no match
        /// </summary>
        /// <param name="name">Current event name</param>
        /// <returns></returns>
        private string[] GetEventId(string name)
        {
            string[] o = {"-1"};
            if (OpenConnection())
            {
                try
                {
                    query = "SELECT id FROM vidburdur WHERE nafn='" + name + "'";

                    mysqlQ = new MySqlCommand(query, link);
                    mysqlR = mysqlQ.ExecuteReader();
                    o = new string[mysqlR.FieldCount];
                    for (int i = 0; mysqlR.Read(); i++)
                        o[i] = mysqlR.GetValue(i).ToString();

                    CloseConnection();
                    UpdateLists();
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show(ex.Message, "Error: " + ex.Number, MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
            return o;
        }

        public void SellTicket(string eventName, string id)
        {
            if (OpenConnection())
            {
                try
                {
                    query =
                            string.Format("DELETE FROM vidburdur "
                          + "WHERE id='{0}'"
                          , id);

                }
                catch (MySqlException ex)
                {
                    MessageBox.Show(ex.Message, "Error: " + ex.Number, MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
        }


        /// <summary>
        /// Reads whole table, output set to ObservationColletion 
        /// </summary>
        public void ReadTable()
        {
            string table = "gestur";
            if (g.Table == 2)
                table = "vidburdur";

            if (OpenConnection())
            {
                try
                {
                    query =
                          "SELECT * "
                        + "FROM " + table;
                    mysqlQ = new MySqlCommand(query, link);
                    mysqlR = mysqlQ.ExecuteReader();

                    while (mysqlR.Read())
                    {
                        if (g.Table == 1)
                            g.Guests.Add(new Guest
                            {
                                SSN = mysqlR.GetValue(0).ToString(),
                                Name = mysqlR.GetValue(1).ToString(),
                                PhoneNumber = mysqlR.GetValue(2).ToString(),
                                Info = mysqlR.GetValue(3).ToString(),
                            });
                        else if (g.Table == 2)
                            g.Events.Add(new Event
                            {
                                Name = mysqlR.GetValue(1).ToString(),
                                Place = mysqlR.GetValue(2).ToString(),
                                Price = mysqlR.GetValue(3).ToString(),
                                Count  = mysqlR.GetValue(4).ToString(),
                                Date = mysqlR.GetValue(5).ToString(),
                                Time = mysqlR.GetValue(6).ToString(),
                            });
                    }
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show(ex.Message, "Error: " + ex.Number, MessageBoxButton.OK, MessageBoxImage.Error);
                }
                CloseConnection();
            }
        }

        /// <summary>
        /// Closes connection retruns true if the connection was closed successfully else returns false
        /// </summary>
        /// <returns></returns>
        private bool OpenConnection()
        {
            try
            {
                link.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message,"Error: " + ex.Number,MessageBoxButton.OK,MessageBoxImage.Error);
                return false;
            }
        }

        /// <summary>
        /// Closes connection retruns true if the connection was closed successfully else returns false
        /// </summary>
        /// <returns></returns>
        private bool CloseConnection()
        {
            try
            {
                link.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Loads all tables user has permission to read
        /// </summary>
        /// <returns>Returns string list of all tables</returns>
        public List<string> GetTableList()
        { 
            List<string> Listinn = new List<string>();

            try
            {
                if (OpenConnection())
                {
                    //fyrirspurn = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'";
                    query = "SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'";
                    
                    mysqlQ = new MySqlCommand(query, link);
                    mysqlR = mysqlQ.ExecuteReader();

                    while (mysqlR.Read())
                        for (int i = 0; i < mysqlR.FieldCount; i++)
                            Listinn.Add(mysqlR.GetValue(i).ToString());
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message, "Error: " + ex.Number, MessageBoxButton.OK, MessageBoxImage.Error);
            }

            CloseConnection();
            return Listinn;
        }

        /// <summary>
        /// Updates the global ObservableCollections
        /// </summary>
        public void UpdateLists()
        {
            int old = g.Table;
            g.Guests.Clear();
            g.Events.Clear();
            for (int i = 1; i < 3; i++)
            {
                g.Table = i;
                ReadTable();
            }
            g.Table = old;
        }
    }
}